package com.cennavi.bigdata.baotou.scripts;

import com.alibaba.fastjson.JSONObject;
import com.mongodb.BasicDBObject;
import com.mongodb.DBCollection;
import com.mongodb.DBObject;
import org.apache.commons.dbcp.BasicDataSource;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by cennavi on 2017/10/26.
 */
public class InertMongoKakouFlowMinute extends ActualTimeRequest{

    private static final org.slf4j.Logger LOGGER = LoggerFactory.getLogger(InertMongoKakouFlowMinute.class);
    private DBCollection correspondingCol = null;

    public Connection getConnection(){
        Connection connection = null;
        try {
            BasicDataSource basicDataSource = new BasicDataSource();
            basicDataSource.setDriverClassName("oracle.jdbc.driver.OracleDriver");
            basicDataSource.setUrl("jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=15.32.12.203)(PORT=1521))(ADDRESS = (PROTOCOL = TCP)(HOST =15.32.12.203 )(PORT = 1521))(LOAD_BALANCE=yes)(CONNECT_DATA=(SERVER = DEDICATED)(SERVICE_NAME=jczhdb)))");
            basicDataSource.setUsername("btjczh");
            basicDataSource.setPassword("btjczh1314");
            connection = basicDataSource.getConnection();
//            System.out.println("数据库连接成功");

        } catch (Exception e) {
            e.printStackTrace();
        }
        return connection;
    }

    public List<String> getDateType(String dateType){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<String> list = new ArrayList<>();
        connection = getConnection();
        String sql;
        if (dateType.equals("work")||dateType.equals("nwork")){
            sql = "select tjsd from N_WORKDAYS";
        }else {
            sql = "select tjsd from N_WORKDAYS where CATEGORYNAME='holi'";
        }
        try {
            statement = connection .prepareStatement(sql);
            resultSet = statement.executeQuery();
            while (resultSet.next()){
                String tjsd = resultSet.getString("tjsd");
                list.add(tjsd);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

//    @Test
//    public void  insertMongoNWorkMinteTest(){
//        String format2 = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(new Date());
//        LOGGER.info("insertMongoNWorkMinte开始时间：" + format2);
//
//        ServerAddress serverAddress = new ServerAddress(prop.getString("mongoHost"), prop.getInteger("mongoPort"));
//        mongoClient = new MongoClient(serverAddress);
//        db = mongoClient.getDB(prop.getString("mongoDb"));
//        correspondingCol = db.getCollection("kakouFlowMinuteWork");
//        BasicDBObject basicDBObject = new BasicDBObject();
//        basicDBObject.put("date","201711");
//        correspondingCol.remove(basicDBObject);
//
//        Connection connection = null;
//        PreparedStatement statement = null;
//        ResultSet resultSet = null;
//
//        Calendar calendar = Calendar.getInstance();
//        calendar.add(Calendar.MONTH,-1);
//        String timeTemp = new SimpleDateFormat("yyyyMM").format(calendar.getTime());
//        String sql = "select tjsd,xjll,xjbh FROM CTL_XJLL_MINUTE_HISTORY_201712  ";
//
////        List<String> tjsdList = getDateType("nwork");
//        Set<String> xjbhSet = new HashSet<>();
//        Set<String> tjsdSet = new HashSet<>();
//        JSONObject xjbhTjsdJson = new JSONObject();
//        connection = getConnection();
//        try {
//            statement = connection.prepareStatement(sql);
//            resultSet = statement.executeQuery();
//            while (resultSet.next()) {
//                String tjsd = resultSet.getString("tjsd");
//                int xjll = resultSet.getInt("xjll");
//                String xjbh = resultSet.getString("xjbh");
////                if (tjsdList.contains(tjsd.split(" ")[0])) {
//                    if (xjbhTjsdJson.containsKey(xjbh)) {
//                        JSONObject reaTjsdSplit = xjbhTjsdJson.getJSONObject(xjbh);
//                        if (reaTjsdSplit.containsKey(tjsd.split(" ")[1])) {
//                            int realXjll = reaTjsdSplit.getInteger(tjsd.split(" ")[1]);
//                            int maxXjll = reaTjsdSplit.getInteger(tjsd.split(" ")[1] + "max");
//                            int count = reaTjsdSplit.getInteger(tjsd.split(" ")[1] + "count");
//                            realXjll += xjll;
//                            count++;
//                            reaTjsdSplit.put(tjsd.split(" ")[1], realXjll / 2);
//                            reaTjsdSplit.put(tjsd.split(" ")[1]+"count", count);
//                            if (maxXjll > xjll) {
//                                reaTjsdSplit.put(tjsd.split(" ")[1] + "max", maxXjll);
//                            } else {
//                                reaTjsdSplit.put(tjsd.split(" ")[1] + "max", xjll);
//                            }
//                            xjbhTjsdJson.put(xjbh, reaTjsdSplit);
//                        } else {
//                            reaTjsdSplit.put(tjsd.split(" ")[1], xjll);
//                            reaTjsdSplit.put(tjsd.split(" ")[1] + "max", xjll);
//                            reaTjsdSplit.put(tjsd.split(" ")[1] + "count", 1);
//                            xjbhTjsdJson.put(xjbh, reaTjsdSplit);
//                        }
//                    } else {
//                        JSONObject jsonObject = new JSONObject();
//                        jsonObject.put(tjsd.split(" ")[1], xjll);
//                        jsonObject.put(tjsd.split(" ")[1] + "max", xjll);
//                        jsonObject.put(tjsd.split(" ")[1] + "count", 1);
//                        xjbhTjsdJson.put(xjbh, jsonObject);
//                    }
////                }
//                tjsdSet.add(tjsd.split(" ")[1]);
//                xjbhSet.add(xjbh);
//            }
//        } catch (SQLException e) {
//            e.printStackTrace();
//        } finally {
//            try {
//                if (resultSet != null) {resultSet.close();}
//                if (statement != null) {statement.close();}
//                if (connection != null) {connection.close();}
//            } catch (Exception e) {
//                e.getStackTrace();
//            } finally {
//                System.out.println("连接已关闭");
//            }
//        }
//        List<DBObject> mongoList = new ArrayList<>();
//        Iterator<String> iterator = xjbhSet.iterator();
//        while (iterator.hasNext()) {
//            String next = iterator.next();
//            if (xjbhTjsdJson.containsKey(next)){
//                JSONObject jsonObject = xjbhTjsdJson.getJSONObject(next);
//                Iterator<String> iterator1 = tjsdSet.iterator();
//                while (iterator1.hasNext()) {
//                    String next1 = iterator1.next();
//                    if (jsonObject.containsKey(next1)) {
//                        int avgxjll = jsonObject.getInteger(next1);
//                        int maxxjll = jsonObject.getInteger(next1 + "max");
//                        int count = jsonObject.getInteger(next1 + "count");
//                        DBObject json = new BasicDBObject();
//                        json.put("kkbh", next);
//                        json.put("time", next1);
//                        json.put("maxKkll", maxxjll);
//                        json.put("avgKkll", avgxjll);
//                        json.put("date", timeTemp);
//                        json.put("dateType", "work");
//                        json.put("count", count);
//                        mongoList.add(json);
//                    }
//                }
//            }
//        }
//        if (mongoList.size() > 0) {
//            correspondingCol.insert(mongoList);
//        }
//        closeConnection();
//        String format = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(new Date());
//        LOGGER.info("insertMongoNWorkMinte完成时间：" + format);
//    }

    public void  insertMongoNWorkMinte(){
        String format2 = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(new Date());
        LOGGER.info("insertMongoNWorkMinte开始时间：" + format2);

        correspondingCol = getMongoCollection("kakouFlowMinuteNWork");
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        Calendar calendar = Calendar.getInstance();
        calendar.add(Calendar.MONTH,-1);
        String timeTemp = new SimpleDateFormat("yyyyMM").format(calendar.getTime());
        String sql = "select tjsd,xjll,xjbh FROM ctl_xjll_minute_history_"+ timeTemp +" order by xjbh ";

        List<String> tjsdList = getDateType("nwork");
        Set<String> xjbhSet = new HashSet<>();
        Set<String> tjsdSet = new HashSet<>();
        JSONObject xjbhTjsdJson = new JSONObject();
        connection = getConnection();
        try {
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                String tjsd = resultSet.getString("tjsd");
                int xjll = resultSet.getInt("xjll");
                String xjbh = resultSet.getString("xjbh");
                if (tjsdList.contains(tjsd.split(" ")[0])) {
                    if (xjbhTjsdJson.containsKey(xjbh)) {
                        JSONObject reaTjsdSplit = xjbhTjsdJson.getJSONObject(xjbh);
                        if (reaTjsdSplit.containsKey(tjsd.split(" ")[1])) {
                            int realXjll = reaTjsdSplit.getInteger(tjsd.split(" ")[1]);
                            int maxXjll = reaTjsdSplit.getInteger(tjsd.split(" ")[1] + "max");
                            int count = reaTjsdSplit.getInteger(tjsd.split(" ")[1] + "count");
                            realXjll += xjll;
                            count++;
                            reaTjsdSplit.put(tjsd.split(" ")[1], realXjll / 2);
                            reaTjsdSplit.put(tjsd.split(" ")[1]+"count", count);
                            if (maxXjll > xjll) {
                                reaTjsdSplit.put(tjsd.split(" ")[1] + "max", maxXjll);
                            } else {
                                reaTjsdSplit.put(tjsd.split(" ")[1] + "max", xjll);
                            }
                            xjbhTjsdJson.put(xjbh, reaTjsdSplit);
                        } else {
                            reaTjsdSplit.put(tjsd.split(" ")[1], xjll);
                            reaTjsdSplit.put(tjsd.split(" ")[1] + "max", xjll);
                            reaTjsdSplit.put(tjsd.split(" ")[1] + "count", 1);
                            xjbhTjsdJson.put(xjbh, reaTjsdSplit);
                        }
                    } else {
                        JSONObject jsonObject = new JSONObject();
                        jsonObject.put(tjsd.split(" ")[1], xjll);
                        jsonObject.put(tjsd.split(" ")[1] + "max", xjll);
                        jsonObject.put(tjsd.split(" ")[1] + "count", 1);
                        xjbhTjsdJson.put(xjbh, jsonObject);
                    }
                }
                tjsdSet.add(tjsd.split(" ")[1]);
                xjbhSet.add(xjbh);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (resultSet != null) {resultSet.close();}
                if (statement != null) {statement.close();}
                if (connection != null) {connection.close();}
            } catch (Exception e) {
                e.getStackTrace();
            } finally {
//                System.out.println("连接已关闭");
            }
        }
        List<DBObject> mongoList = new ArrayList<>();
        Iterator<String> iterator = xjbhSet.iterator();
        while (iterator.hasNext()) {
            String next = iterator.next();
            if (xjbhTjsdJson.containsKey(next)){
                JSONObject jsonObject = xjbhTjsdJson.getJSONObject(next);
                Iterator<String> iterator1 = tjsdSet.iterator();
                while (iterator1.hasNext()) {
                    String next1 = iterator1.next();
                    if (jsonObject.containsKey(next1)) {
                        int avgxjll = jsonObject.getInteger(next1);
                        int maxxjll = jsonObject.getInteger(next1 + "max");
                        int count = jsonObject.getInteger(next1 + "count");
                        DBObject json = new BasicDBObject();
                        json.put("kkbh", next);
                        json.put("time", next1);
                        json.put("maxKkll", maxxjll);
                        json.put("avgKkll", avgxjll);
                        json.put("date", timeTemp);
//                        json.put("date", "201710");
                        json.put("dateType", "nwork");
                        json.put("count", count);
                        mongoList.add(json);
                    }
                }
            }
        }
        if (mongoList.size() > 0) {
            correspondingCol.insert(mongoList);
        }
        String format = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(new Date());
        LOGGER.info("insertMongoNWorkMinte完成时间：" + format);
        insertMongoWorkMinte();
        insertMongoHoliMinte();
    }

    //分钟工作日表（12个月）
    public void  insertMongoWorkMinte(){

        String format2 = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(new Date());
        LOGGER.info("insertMongoWorkMinte开始时间：" + format2);
        correspondingCol = getMongoCollection("kakouFlowMinuteWork");
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        Calendar calendar = Calendar.getInstance();
        calendar.add(Calendar.MONTH,-1);
        String timeTemp = new SimpleDateFormat("yyyyMM").format(calendar.getTime());
        String sql = "select tjsd,xjll,xjbh FROM ctl_xjll_minute_history_"+ timeTemp +" order by xjbh ";

        List<String> tjsdList = getDateType("work");

        Set<String> xjbhSet = new HashSet<>();
        Set<String> tjsdSet = new HashSet<>();
        JSONObject xjbhTjsdJson = new JSONObject();
        connection = getConnection();
        try {
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                String tjsd = resultSet.getString("tjsd");
                int xjll = resultSet.getInt("xjll");
                String xjbh = resultSet.getString("xjbh");
                if (!tjsdList.contains(tjsd.split(" ")[0])) {
                    if (xjbhTjsdJson.containsKey(xjbh)) {
                        JSONObject reaTjsdSplit = xjbhTjsdJson.getJSONObject(xjbh);
                        if (reaTjsdSplit.containsKey(tjsd.split(" ")[1])) {
                            int realXjll = reaTjsdSplit.getInteger(tjsd.split(" ")[1]);
                            int maxXjll = reaTjsdSplit.getInteger(tjsd.split(" ")[1] + "max");
                            int count = reaTjsdSplit.getInteger(tjsd.split(" ")[1] + "count");
                            realXjll += xjll;
                            count++;
                            reaTjsdSplit.put(tjsd.split(" ")[1], realXjll / 2);
                            reaTjsdSplit.put(tjsd.split(" ")[1]+"count", count);
                            if (maxXjll > xjll) {
                                reaTjsdSplit.put(tjsd.split(" ")[1] + "max", maxXjll);
                            } else {
                                reaTjsdSplit.put(tjsd.split(" ")[1] + "max", xjll);
                            }
                            xjbhTjsdJson.put(xjbh, reaTjsdSplit);
                        } else {
                            reaTjsdSplit.put(tjsd.split(" ")[1], xjll);
                            reaTjsdSplit.put(tjsd.split(" ")[1] + "max", xjll);
                            reaTjsdSplit.put(tjsd.split(" ")[1] + "count", 1);
                            xjbhTjsdJson.put(xjbh, reaTjsdSplit);
                        }
                    } else {
                        JSONObject jsonObject = new JSONObject();
                        jsonObject.put(tjsd.split(" ")[1], xjll);
                        jsonObject.put(tjsd.split(" ")[1] + "max", xjll);
                        jsonObject.put(tjsd.split(" ")[1] + "count", 1);
                        xjbhTjsdJson.put(xjbh, jsonObject);
                    }
                }
                tjsdSet.add(tjsd.split(" ")[1]);
                xjbhSet.add(xjbh);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (resultSet != null) {resultSet.close();}
                if (statement != null) {statement.close();}
                if (connection != null) {connection.close();}
            } catch (Exception e) {
                e.getStackTrace();
            } finally {
//                System.out.println("连接已关闭");
            }
        }
        List<DBObject> mongoList = new ArrayList<>();
        Iterator<String> iterator = xjbhSet.iterator();
        while (iterator.hasNext()) {
            String next = iterator.next();
            if (xjbhTjsdJson.containsKey(next)){
                JSONObject jsonObject = xjbhTjsdJson.getJSONObject(next);
                Iterator<String> iterator1 = tjsdSet.iterator();
                while (iterator1.hasNext()) {
                    String next1 = iterator1.next();
                    if (jsonObject.containsKey(next1)) {
                        int avgxjll = jsonObject.getInteger(next1);
                        int maxxjll = jsonObject.getInteger(next1 + "max");
                        int count = jsonObject.getInteger(next1 + "count");
                        DBObject json = new BasicDBObject();
                        json.put("kkbh", next);
                        json.put("time", next1);
                        json.put("maxKkll", maxxjll);
                        json.put("avgKkll", avgxjll);
                        json.put("date", timeTemp);
//                        json.put("date", "201710");
                        json.put("dateType", "work");
                        json.put("count", count);
                        mongoList.add(json);
                    }
                }
            }
        }
        if (mongoList.size() > 0) {
            correspondingCol.insert(mongoList);
        }
        String format = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(new Date());
        LOGGER.info("insertMongoWorkMinte完成时间：" + format);
    }

    public void  insertMongoHoliMinte(){

        String format2 = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(new Date());
        LOGGER.info("insertMongoHoliMinte开始时间：" + format2);
        correspondingCol = getMongoCollection("kakouFlowMinuteHoli");
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        Calendar calendar = Calendar.getInstance();
        calendar.add(Calendar.MONTH,-1);
        String timeTemp = new SimpleDateFormat("yyyyMM").format(calendar.getTime());
        String sql = "select tjsd,xjll,xjbh FROM ctl_xjll_minute_history_"+ timeTemp +" order by xjbh ";
        List<String> tjsdList = getDateType("holi");

        Set<String> xjbhSet = new HashSet<>();
        Set<String> tjsdSet = new HashSet<>();
        JSONObject xjbhTjsdJson = new JSONObject();
        connection = getConnection();
        try {
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                String tjsd = resultSet.getString("tjsd");
                int xjll = resultSet.getInt("xjll");
                String xjbh = resultSet.getString("xjbh");
                if (tjsdList.contains(tjsd.split(" ")[0])) {
                    if (xjbhTjsdJson.containsKey(xjbh)) {
                        JSONObject reaTjsdSplit = xjbhTjsdJson.getJSONObject(xjbh);
                        if (reaTjsdSplit.containsKey(tjsd.split(" ")[1])) {
                            int realXjll = reaTjsdSplit.getInteger(tjsd.split(" ")[1]);
                            int maxXjll = reaTjsdSplit.getInteger(tjsd.split(" ")[1] + "max");
                            int count = reaTjsdSplit.getInteger(tjsd.split(" ")[1] + "count");
                            realXjll += xjll;
                            count++;
                            reaTjsdSplit.put(tjsd.split(" ")[1], realXjll / 2);
                            reaTjsdSplit.put(tjsd.split(" ")[1]+"count", count);
                            if (maxXjll > xjll) {
                                reaTjsdSplit.put(tjsd.split(" ")[1] + "max", maxXjll);
                            } else {
                                reaTjsdSplit.put(tjsd.split(" ")[1] + "max", xjll);
                            }
                            xjbhTjsdJson.put(xjbh, reaTjsdSplit);
                        } else {
                            reaTjsdSplit.put(tjsd.split(" ")[1], xjll);
                            reaTjsdSplit.put(tjsd.split(" ")[1] + "max", xjll);
                            reaTjsdSplit.put(tjsd.split(" ")[1] + "count", 1);
                            xjbhTjsdJson.put(xjbh, reaTjsdSplit);
                        }
                    } else {
                        JSONObject jsonObject = new JSONObject();
                        jsonObject.put(tjsd.split(" ")[1], xjll);
                        jsonObject.put(tjsd.split(" ")[1] + "max", xjll);
                        jsonObject.put(tjsd.split(" ")[1] + "count", 1);
                        xjbhTjsdJson.put(xjbh, jsonObject);
                    }
                }
                tjsdSet.add(tjsd.split(" ")[1]);
                xjbhSet.add(xjbh);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (resultSet != null) {resultSet.close();}
                if (statement != null) {statement.close();}
                if (connection != null) {connection.close();}
            } catch (Exception e) {
                e.getStackTrace();
            } finally {
//                System.out.println("连接已关闭");
            }
        }
        List<DBObject> mongoList = new ArrayList<>();
        Iterator<String> iterator = xjbhSet.iterator();
        while (iterator.hasNext()) {
            String next = iterator.next();
            if (xjbhTjsdJson.containsKey(next)){
                JSONObject jsonObject = xjbhTjsdJson.getJSONObject(next);
                Iterator<String> iterator1 = tjsdSet.iterator();
                while (iterator1.hasNext()) {
                    String next1 = iterator1.next();
                    if (jsonObject.containsKey(next1)) {
                        int avgxjll = jsonObject.getInteger(next1);
                        int maxxjll = jsonObject.getInteger(next1 + "max");
                        int count = jsonObject.getInteger(next1 + "count");
                        DBObject json = new BasicDBObject();
                        json.put("kkbh", next);
                        json.put("time", next1);
                        json.put("maxKkll", maxxjll);
                        json.put("avgKkll", avgxjll);
                        json.put("date", timeTemp);
//                        json.put("date", "201710");
                        json.put("dateType", "holi");
                        json.put("count", count);
                        mongoList.add(json);
                    }
                }
            }
        }
        if (mongoList.size() > 0) {
            correspondingCol.insert(mongoList);
        }
        String format = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(new Date());
        LOGGER.info("insertMongoHoliMinte完成时间：" + format);
        closeMongo();
    }
}